package net.gz01.wallet.web.service;


import java.io.File;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

import net.gz01.util.ZOMoney;
import net.gz01.wallet.web.config.BalanceInfo;
import org.jxls.common.Context;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import lombok.extern.slf4j.Slf4j;
import net.gz01.util.ZODate;
import net.gz01.util.ZOExcel;
import net.gz01.wallet.web.config.SystemEnum;
import net.gz01.wallet.web.config.SystemEnum.FilePath;
import net.gz01.wallet.web.model.Account;
import net.gz01.wallet.web.model.ClearingIncome;
import net.gz01.wallet.web.model.TradeOrder;

@Slf4j
public class ReportService {
	private static final String REPORT_PATH = SystemEnum.FilePath.ReportTmp.getValue();
	private static final String TEMPLATE = SystemEnum.FilePath.ReportTemplate.getValue();



	public static String changeF2Y(Long money) {
		money = money == null ? 0 : money;
		BigDecimal numOne = BigDecimal.valueOf(money);
		numOne = numOne.divide(BigDecimal.valueOf(100)).setScale(2);//必须保留2位小数点
		return String.valueOf(numOne);
	}


	private String showFormt(Long money){
		return new DecimalFormat(",###,###,##0.00").format(money/100.0);
	}

	/**
	 * 虚账余额报表
	 *
	 * @return
	 */
	public File virtualBalance(List<String> filePath, List<BalanceInfo> listInfo) {
		// 友利当前余额
		Record youli = Db.findFirst("SELECT SUM(balance) AS balance FROM account WHERE accountNo=?", Account.default_merchCode);
		// 客户当前余额
		Record customer = Db.findFirst("SELECT SUM(balance) AS balance FROM account WHERE accountNo<>?", Account.default_merchCode);
		if (youli == null || customer == null) {
			log.info("无数据");
			return null;
		}
		// 友利当前余额
		Long youliBalance = youli.getBigDecimal("balance") == null ? 0L : youli.getBigDecimal("balance").longValue();
		// 友利今日支出
		Record youliExpenseRecord = Db.findFirst("select sum(amount+fee) as youliExpense from trade_order where settleDate=? and merchCode=? and bizType in ('df_personal','df_company','withdraw_personal','withdraw_company') and orderStatus in('TS')", ZODate.getDate(), Account.default_merchCode);
		Long youliExpense = youliExpenseRecord.getBigDecimal("youliExpense") == null ? 0L : youliExpenseRecord.getBigDecimal("youliExpense").longValue();
		youli.set("name", "运营资金");
		youli.set("accountNo", Account.default_merchCode);
		youli.set("youliEarning", 0L);
		youli.set("youliExpense", youliExpense);
		youli.set("youliTodoExpense", 0L);
		youli.set("youliYesterdayBalance", youliBalance - youliExpense);
		youli.set("youliBalance", youliBalance);
		// 客户当前余额
		Long customerBalance = customer.getBigDecimal("balance") == null ? 0L : customer.getBigDecimal("balance").longValue();
		// 客户今日收入
		Record customerEarningRecord = Db.findFirst("select sum(amount) as customerEarning from trade_order where settleDate=? and merchCode<>? and bizType='charge' and orderStatus='TS'", ZODate.getDate(), Account.default_merchCode);
		Long customerEarning = customerEarningRecord.getBigDecimal("customerEarning") == null ? 0L : Math.abs(customerEarningRecord.getBigDecimal("customerEarning").longValue());
		// 客户今日支出
		Record customerExpenseRecord = Db.findFirst("select sum(amount) as customerExpense from trade_order where settleDate=? and merchCode<>? and bizType in ('df_personal','df_company','withdraw_personal','withdraw_company') and orderStatus='TS'", ZODate.getDate(), Account.default_merchCode);
		Long customerExpense = customerExpenseRecord.getBigDecimal("customerExpense") == null ? 0L :  Math.abs(customerExpenseRecord.getBigDecimal("customerExpense").longValue());
		//客户今日处理中金额
		Record customerTodoExpenseRecord = Db.findFirst("select sum(amount) as customerTodoExpense from trade_order where merchCode<>? and bizType in ('df_personal','df_company','withdraw_personal','withdraw_company') and orderStatus in('R1','R2','R3','2F','3F','WT')", Account.default_merchCode);
		Long customerTodoExpense = customerTodoExpenseRecord.getBigDecimal("customerTodoExpense") == null ? 0L : Math.abs(customerTodoExpenseRecord.getBigDecimal("customerTodoExpense").longValue());
//
//



		LocalDate today = LocalDate.now();
		LocalDate firstday = LocalDate.of(today.getYear(),today.getMonth(),1);
		//本月的最后一天
		LocalDate lastDay =today.with(TemporalAdjusters.lastDayOfMonth());

// 本月收取的手续费金额
		Record customerFeeRecord = Db.findFirst("select sum(fee) as customerFee from trade_order where settleDate BETWEEN ? and ? and merchCode<>? and bizType in ('df_personal','df_company','withdraw_personal','withdraw_company') and orderStatus='TS'", firstday.toString(), lastDay.toString(), Account.default_merchCode);
		Long customerFee = customerFeeRecord.getBigDecimal("customerFee") == null ? 0L :  Math.abs(customerFeeRecord.getBigDecimal("customerFee").longValue());

//本月处理中的手续费
		Record customerTodoFeeRecord = Db.findFirst("select sum(fee) as customerTodoFee from trade_order where merchCode<>? and bizType in ('df_personal','df_company','withdraw_personal','withdraw_company') and orderStatus in('R1','R2','R3','2F','3F','WT')", Account.default_merchCode);
		Long customerTodoFee = customerTodoFeeRecord.getBigDecimal("customerTodoFee") == null ? 0L : Math.abs(customerTodoFeeRecord.getBigDecimal("customerTodoFee").longValue());



		customer.set("name", "业务资金");
		customer.set("accountNo", "-");
		customer.set("customerEarning", customerEarning);//本日收入金额
		customer.set("customerExpense", customerExpense);//本日付款支出成功的金额
		customer.set("customerTodoExpense", customerTodoExpense);//付款支出待处理金额
		Long customerYesterdayBalance = customerBalance + customerExpense + customerTodoExpense - customerEarning;//前日余额
		customerYesterdayBalance = customerYesterdayBalance < 0L? 0L : customerYesterdayBalance;
		customer.set("customerYesterdayBalance", customerYesterdayBalance);
		customer.set("customerBalance", customerBalance);//本日余额

		if(null != listInfo) {
			{
				BalanceInfo balanceInfo = new BalanceInfo();
				balanceInfo.setName("前日余额");
				balanceInfo.setBalance(changeF2Y((customerYesterdayBalance)));
				balanceInfo.setShowBalance(showFormt(customerYesterdayBalance));
				listInfo.add(balanceInfo);

				balanceInfo = new BalanceInfo();
				balanceInfo.setName("本日收入");
				balanceInfo.setBalance(changeF2Y(customerEarning));
				balanceInfo.setShowBalance(showFormt(customerEarning));
				listInfo.add(balanceInfo);

				balanceInfo = new BalanceInfo();
				balanceInfo.setName("本日支出");
				balanceInfo.setBalance(changeF2Y(customerExpense));
				balanceInfo.setShowBalance(showFormt(customerExpense));
				listInfo.add(balanceInfo);

				balanceInfo = new BalanceInfo();
				balanceInfo.setName("本日待处理支出");
				balanceInfo.setBalance(changeF2Y(customerTodoExpense));
				balanceInfo.setShowBalance(showFormt(customerTodoExpense));
				listInfo.add(balanceInfo);

				balanceInfo = new BalanceInfo();
				balanceInfo.setName("本日余额");
				balanceInfo.setShowBalance(showFormt(customerBalance));
				balanceInfo.setBalance(changeF2Y(customerBalance));
				listInfo.add(balanceInfo);


				balanceInfo = new BalanceInfo();
				balanceInfo.setName("本月手续费");
				balanceInfo.setShowBalance(showFormt(customerFee));
				balanceInfo.setBalance(changeF2Y(customerFee));
				listInfo.add(balanceInfo);


				balanceInfo = new BalanceInfo();
				balanceInfo.setName("本月处理中手续费");
				balanceInfo.setShowBalance(showFormt(customerTodoFee));
				balanceInfo.setBalance(changeF2Y(customerTodoFee));
				listInfo.add(balanceInfo);

			}

			return null;
		}


		Context context1 = new Context();
		context1.putVar("youli", youli);
		context1.putVar("customer", customer);

		List<Account> dataList = Account.dao.find("SELECT * FROM account WHERE balance > 0 ORDER BY balance DESC");
		Context context2 = new Context();
		context2.putVar("list2", dataList);

		String createTime = ZODate.getDateTimeNumber();
		String templatePath = TEMPLATE + "wallet_balance.xlsx";
		String fileName = "wallet_balance_" + createTime + ".xlsx";
		String targetPath = REPORT_PATH + fileName;
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("虚账余额汇总" + ZODate.getDate(), context1);
		sheetNameAndData.put("虚账余额明细", context2);
		String[] templateSheetName = {"sheet1", "sheet2"};

		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出虚账余额报表发生异常" + e);
		}
		File file = new File(targetPath);
		if(filePath!=null){
			filePath.add(fileName);
		}
		return file;
	}

	public File income(String startSettleDate, String endSettleDate) {
		List<ClearingIncome> list = ClearingIncome.dao.find("SELECT * FROM clearing_income WHERE type='0' AND settleDate BETWEEN ? AND ?", startSettleDate, endSettleDate);
		if (list.isEmpty()) {
			return null;
		}
		Context context = new Context();
		context.putVar("list", list);
		String createTime = ZODate.getDateTimeNumber();
		String templatePath = TEMPLATE + "income.xlsx";
		String targetPath = REPORT_PATH + "收入" + createTime + ".xlsx";
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("渠道收入", context);
		String[] templateSheetName = {"sheet1"};

		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出渠道收入报表发生异常" + e);
		}
		File file = new File(targetPath);
		return file;
	}

	public File share(String startSettleDate, String endSettleDate) {
		List<ClearingIncome> list = ClearingIncome.dao.find("SELECT * FROM clearing_income WHERE type='1' AND settleDate BETWEEN ? AND ?", startSettleDate, endSettleDate);
		if (list.isEmpty()) {
			return null;
		}
		Context context = new Context();
		context.putVar("list", list);
		String createTime = ZODate.getDateTimeNumber();
		String templatePath = TEMPLATE + "share.xlsx";
		String targetPath = REPORT_PATH + "机构分润" + createTime + ".xlsx";
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("机构分润", context);
		String[] templateSheetName = {"sheet1"};

		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出机构分润报表发生异常" + e);
		}
		File file = new File(targetPath);
		return file;
	}

	public File tradeDetail(String startSettleDate, String endSettleDate) {
		List<TradeOrder> orderList = TradeOrder.dao.searchTradeDetailReport(startSettleDate, endSettleDate);
		if (orderList.isEmpty()) {
			return null;
		}
		for (TradeOrder order : orderList) {
			BigDecimal amount = BigDecimal.valueOf(order.getAmount()).divide(BigDecimal.valueOf(100));
			BigDecimal fee = BigDecimal.valueOf(order.getFee()).divide(BigDecimal.valueOf(100));
			BigDecimal balance = BigDecimal.valueOf(order.getBalance()).divide(BigDecimal.valueOf(100));
			if("df_personal".equals(order.getBizType())) {
				order.put("amountY", amount.divide(new BigDecimal(-1)));
				order.setBizType("对私代付");
			} else if("df_company".equals(order.getBizType())) {
				order.put("amountY", amount.divide(new BigDecimal(-1)));
				order.setBizType("对公代付");
			} else{
				order.put("amountY", amount);
				order.setBizType("充值");
			}
			order.put("feeY",fee.divide(new BigDecimal(-1)));
			order.put("balanceY", balance);
		}

		String fileName = "充值明细_" + startSettleDate + "至" + endSettleDate;
		Context context = new Context();
		context.putVar("fileName", fileName);
		context.putVar("list", orderList);
		String templatePath = FilePath.ReportTemplate.getValue() + "chargepay.xlsx";
		String targetPath = FilePath.ReportTmp.getValue() + fileName + ".xlsx";
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("充值明细", context);
		String[] templateSheetName = { "sheet1" };
		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出渠道交易明细报表发生异常");
			e.printStackTrace();
		}
		return new File(targetPath);
	}

}
